/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : 127.0.0.1:3306
 Source Schema         : education

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 19/01/2022 11:09:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for choose_course
-- ----------------------------
DROP TABLE IF EXISTS `choose_course`;
CREATE TABLE `choose_course` (
  `user_id` int NOT NULL,
  `course_id` int NOT NULL,
  `usual_grade` double(4,1) DEFAULT NULL,
  `end_grade` double(4,1) DEFAULT NULL,
  `total_grade` double(4,1) DEFAULT NULL,
  `choose_status` int NOT NULL DEFAULT '0',
  `is_pass` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`course_id`),
  KEY `fk_course` (`course_id`) USING BTREE,
  CONSTRAINT `fk_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_student` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of choose_course
-- ----------------------------
BEGIN;
INSERT INTO `choose_course` VALUES (4, 22, 66.0, 99.0, 85.8, 1, '是');
INSERT INTO `choose_course` VALUES (4, 24, NULL, NULL, NULL, 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `course_id` int NOT NULL AUTO_INCREMENT,
  `course_code` varchar(255) NOT NULL,
  `course_name` varchar(255) NOT NULL,
  `course_type` varchar(255) NOT NULL,
  `school_id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_status` int NOT NULL DEFAULT '0',
  `course_term_id` int NOT NULL,
  `course_term` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `course_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `course_place` varchar(255) NOT NULL,
  `course_class` int NOT NULL,
  `course_credit` int NOT NULL,
  `usual_weight` double NOT NULL DEFAULT '0.4',
  `end_weight` double NOT NULL DEFAULT '0.6',
  PRIMARY KEY (`course_id`),
  KEY `fk_school` (`school_id`),
  KEY `fk_teacher` (`teacher_id`),
  KEY `fk_item` (`course_term_id`),
  CONSTRAINT `fk_item` FOREIGN KEY (`course_term_id`) REFERENCES `term` (`term_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_school` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `user` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES (22, '0011', 'springboot', '限选', 1, 2, 0, 12, '2021-2022学年第二学期', '1-17周 周一1-2节', '9-101', 1, 4, 0.4, 0.6);
INSERT INTO `course` VALUES (23, '0001', 'java程序设计', '选修', 1, 2, 0, 12, '2021-2022学年第二学期', '1-17周 周一3-4节', '9-404', 1, 3, 0.3, 0.7);
INSERT INTO `course` VALUES (24, '0022', 'vue开发', '限选', 1, 2, 1, 12, '2021-2022学年第二学期', '1-17周 周一1-2节', '8-402', 1, 3, 0.5, 0.5);
COMMIT;

-- ----------------------------
-- Table structure for permission
-- ----------------------------
DROP TABLE IF EXISTS `permission`;
CREATE TABLE `permission` (
  `permission_id` int NOT NULL AUTO_INCREMENT,
  `permission_code` varchar(20) NOT NULL,
  `permission_name` varchar(20) NOT NULL,
  `father_id` int NOT NULL,
  `path` varchar(255) NOT NULL,
  `is_menu` int NOT NULL,
  PRIMARY KEY (`permission_id`),
  UNIQUE KEY `permission_code` (`permission_code`),
  UNIQUE KEY `permission_name` (`permission_name`)
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of permission
-- ----------------------------
BEGIN;
INSERT INTO `permission` VALUES (1, 'course', '选课相关', 0, '/course', 1);
INSERT INTO `permission` VALUES (2, 'course:choose', '选课', 1, '/course/choose', 1);
INSERT INTO `permission` VALUES (3, 'course:drop', '退课', 1, '/course/drop', 0);
INSERT INTO `permission` VALUES (4, 'course:result', '选课结果', 1, '/course/result', 1);
INSERT INTO `permission` VALUES (5, 'course:history', '历史课程', 1, '/course/history', 1);
INSERT INTO `permission` VALUES (6, 'grade', '成绩相关', 0, '/grade', 1);
INSERT INTO `permission` VALUES (7, 'grade:this', '本学期成绩', 6, '/grade/this', 1);
INSERT INTO `permission` VALUES (8, 'grade:history', '历史成绩', 6, '/grade/history', 1);
INSERT INTO `permission` VALUES (9, 'grade_manage', '成绩管理', 0, '/grade_manage', 0);
INSERT INTO `permission` VALUES (10, 'grade_manage:in', '录入成绩', 9, '/grade_manage/in', 0);
INSERT INTO `permission` VALUES (11, 'course_start', '查看开课', 0, '/course_start', 1);
INSERT INTO `permission` VALUES (12, 'course_start:history', '历史开课', 11, '/course_start/history', 1);
INSERT INTO `permission` VALUES (13, 'course_start:this', '本学期开课', 11, '/course_start/this', 1);
INSERT INTO `permission` VALUES (14, 'showCourseStudent', '查看课程学生名单', 11, '/showCourseStudent', 0);
INSERT INTO `permission` VALUES (15, 'course_manage', '课程管理', 0, '/course_manage', 1);
INSERT INTO `permission` VALUES (16, 'course_manage:add', '增加课程', 15, '/course_manage/add', 1);
INSERT INTO `permission` VALUES (17, 'course_manage:look', '查看课程', 15, '/course_manage/look', 1);
INSERT INTO `permission` VALUES (18, 'course_manage:delete', '删除课程', 15, '/course_manage/delete', 0);
INSERT INTO `permission` VALUES (19, 'course_manage:update', '修改课程', 15, '/course_manage/update', 0);
INSERT INTO `permission` VALUES (20, 'term', '学期管理', 0, '/term', 0);
INSERT INTO `permission` VALUES (21, 'term:set', '设置当前学期', 22, '/choose_manage/set', 1);
INSERT INTO `permission` VALUES (22, 'choose_manage', '选课管理', 0, '/choose_manage', 1);
INSERT INTO `permission` VALUES (23, 'choose_manage:open', '开放选课', 22, '/choose_manage/open', 1);
INSERT INTO `permission` VALUES (24, 'choose_manage:close', '关闭选课', 22, '/choose_manage/close', 0);
INSERT INTO `permission` VALUES (25, 'school', '学院管理', 0, '/school', 1);
INSERT INTO `permission` VALUES (26, 'school:manager', '管理学院', 25, '/school/manager', 1);
INSERT INTO `permission` VALUES (27, 'school:update', '修改学院', 25, '/school/update', 0);
INSERT INTO `permission` VALUES (28, 'school:delete', '删除学院', 25, '/school/delete', 0);
INSERT INTO `permission` VALUES (29, 'school:add', '增加学院', 25, '/school/add', 1);
INSERT INTO `permission` VALUES (30, 'user', '角色管理', 0, '/user', 1);
INSERT INTO `permission` VALUES (31, 'user:add', '角色增加', 30, '/user/add', 1);
INSERT INTO `permission` VALUES (32, 'user:manager', '管理角色', 30, '/user/manager', 1);
INSERT INTO `permission` VALUES (33, 'user:update', '修改角色', 30, '/user/update', 0);
INSERT INTO `permission` VALUES (34, 'user:delete', '删除角色', 30, '/user/delete', 0);
INSERT INTO `permission` VALUES (35, 'user:role', '角色赋权', 30, '/user/permission', 1);
COMMIT;

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `role_id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(20) NOT NULL,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of role
-- ----------------------------
BEGIN;
INSERT INTO `role` VALUES (1, '学生');
INSERT INTO `role` VALUES (3, '教务');
INSERT INTO `role` VALUES (2, '教师');
INSERT INTO `role` VALUES (4, '系统管理员');
COMMIT;

-- ----------------------------
-- Table structure for role_permission
-- ----------------------------
DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission` (
  `role_id` int NOT NULL,
  `permission_id` int NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `fk_permission` (`permission_id`),
  CONSTRAINT `fk_permission` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`permission_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_role_p` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of role_permission
-- ----------------------------
BEGIN;
INSERT INTO `role_permission` VALUES (1, 1);
INSERT INTO `role_permission` VALUES (1, 2);
INSERT INTO `role_permission` VALUES (1, 3);
INSERT INTO `role_permission` VALUES (1, 4);
INSERT INTO `role_permission` VALUES (1, 5);
INSERT INTO `role_permission` VALUES (1, 6);
INSERT INTO `role_permission` VALUES (1, 7);
INSERT INTO `role_permission` VALUES (1, 8);
INSERT INTO `role_permission` VALUES (2, 9);
INSERT INTO `role_permission` VALUES (2, 10);
INSERT INTO `role_permission` VALUES (2, 11);
INSERT INTO `role_permission` VALUES (2, 12);
INSERT INTO `role_permission` VALUES (2, 13);
INSERT INTO `role_permission` VALUES (2, 14);
INSERT INTO `role_permission` VALUES (3, 14);
INSERT INTO `role_permission` VALUES (3, 15);
INSERT INTO `role_permission` VALUES (3, 16);
INSERT INTO `role_permission` VALUES (3, 17);
INSERT INTO `role_permission` VALUES (3, 18);
INSERT INTO `role_permission` VALUES (3, 19);
INSERT INTO `role_permission` VALUES (4, 20);
INSERT INTO `role_permission` VALUES (4, 21);
INSERT INTO `role_permission` VALUES (4, 22);
INSERT INTO `role_permission` VALUES (4, 23);
INSERT INTO `role_permission` VALUES (4, 24);
INSERT INTO `role_permission` VALUES (4, 25);
INSERT INTO `role_permission` VALUES (4, 26);
INSERT INTO `role_permission` VALUES (4, 27);
INSERT INTO `role_permission` VALUES (4, 28);
INSERT INTO `role_permission` VALUES (4, 29);
INSERT INTO `role_permission` VALUES (4, 30);
INSERT INTO `role_permission` VALUES (4, 31);
INSERT INTO `role_permission` VALUES (4, 32);
INSERT INTO `role_permission` VALUES (4, 33);
INSERT INTO `role_permission` VALUES (4, 34);
INSERT INTO `role_permission` VALUES (4, 35);
COMMIT;

-- ----------------------------
-- Table structure for school
-- ----------------------------
DROP TABLE IF EXISTS `school`;
CREATE TABLE `school` (
  `school_id` int NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) NOT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of school
-- ----------------------------
BEGIN;
INSERT INTO `school` VALUES (1, '计算机学院');
INSERT INTO `school` VALUES (3, '网络空间安全学院');
INSERT INTO `school` VALUES (30, '护理学院');
INSERT INTO `school` VALUES (32, '软件学院');
COMMIT;

-- ----------------------------
-- Table structure for term
-- ----------------------------
DROP TABLE IF EXISTS `term`;
CREATE TABLE `term` (
  `term_id` int NOT NULL AUTO_INCREMENT,
  `term_name` varchar(255) NOT NULL,
  `term_status` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`term_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of term
-- ----------------------------
BEGIN;
INSERT INTO `term` VALUES (1, '2016-2017学年第一学期', 0);
INSERT INTO `term` VALUES (2, '2016-2017学年第二学期', 0);
INSERT INTO `term` VALUES (3, '2017-2018学年第一学期', 0);
INSERT INTO `term` VALUES (4, '2017-2018学年第二学期', 0);
INSERT INTO `term` VALUES (5, '2018-2019学年第一学期', 0);
INSERT INTO `term` VALUES (6, '2018-2019学年第二学期', 0);
INSERT INTO `term` VALUES (7, '2019-2020学年第一学期', 0);
INSERT INTO `term` VALUES (8, '2019-2020学年第二学期', 0);
INSERT INTO `term` VALUES (9, '2020-2021学年第一学期', 0);
INSERT INTO `term` VALUES (10, '2020-2021学年第二学期', 0);
INSERT INTO `term` VALUES (11, '2021-2022学年第一学期', 0);
INSERT INTO `term` VALUES (12, '2021-2022学年第二学期', 1);
INSERT INTO `term` VALUES (13, '2022-2023学年第一学期', 0);
INSERT INTO `term` VALUES (14, '2022-2023学年第二学期', 0);
INSERT INTO `term` VALUES (15, '2023-2024学年第一学期', 0);
INSERT INTO `term` VALUES (16, '2023-2024学年第二学期', 0);
INSERT INTO `term` VALUES (17, '2024-2025学年第一学期', 0);
INSERT INTO `term` VALUES (18, '2024-2025学年第二学期', 0);
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(255) NOT NULL,
  `salt` varchar(255) NOT NULL,
  `nickname` varchar(255) NOT NULL,
  `school_id` int NOT NULL,
  `sex` varchar(255) NOT NULL,
  `tel` varchar(100) NOT NULL,
  `student_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`) USING BTREE,
  KEY `fk_sch` (`school_id`),
  CONSTRAINT `fk_sch` FOREIGN KEY (`school_id`) REFERENCES `school` (`school_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, 'root', '93fa78ccadae0c317ceadf9fcfd56336', 'salt', '管理员', 1, '男', '18436022222', NULL);
INSERT INTO `user` VALUES (2, 'teacher', '4d6dd2327040859a7fb9287081048bc4', 'salt', '教师', 1, '女', '18459585625', NULL);
INSERT INTO `user` VALUES (3, 'admin', '3042d7230c565a47d72a21d39759cb3d', 'salt', '教务', 1, '男', '15269859658', NULL);
INSERT INTO `user` VALUES (4, 'student', 'fb1ad1670cf45e1f51a34bfe27f0e852', 'salt', '学生', 1, '男', '17589658625', '计算机2018-01班');
INSERT INTO `user` VALUES (45, 'user01', 'ccd07cd3a878582bceafe08934de000b', 'sss', '学生01', 1, '男', '15986589563', '3班');
COMMIT;

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_id` int NOT NULL,
  `role_id` int NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `fk_role` (`role_id`),
  CONSTRAINT `fk_role` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of user_role
-- ----------------------------
BEGIN;
INSERT INTO `user_role` VALUES (4, 1);
INSERT INTO `user_role` VALUES (45, 1);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (3, 3);
INSERT INTO `user_role` VALUES (1, 4);
COMMIT;

-- ----------------------------
-- Triggers structure for table choose_course
-- ----------------------------
DROP TRIGGER IF EXISTS `tri_choose_course_update`;
delimiter ;;
CREATE TRIGGER `tri_choose_course_update` BEFORE UPDATE ON `choose_course` FOR EACH ROW BEGIN
	DECLARE endWe DOUBLE;
	DECLARE usualWe DOUBLE;
	SET endWe = (SELECT end_weight FROM course WHERE course_id = new.course_id);
	SET usualWe = (SELECT usual_weight FROM course WHERE course_id = new.course_id);
	SET new.total_grade = new.usual_grade*usualWe + new.end_grade*endWe;
	IF (new.total_grade>=60) THEN
		SET new.is_pass = '是' ;
	END IF;
	IF (new.total_grade<60)  THEN
		SET new.is_pass = '否' ;
	END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table course
-- ----------------------------
DROP TRIGGER IF EXISTS `tri_course_insert`;
delimiter ;;
CREATE TRIGGER `tri_course_insert` BEFORE INSERT ON `course` FOR EACH ROW BEGIN
	declare c int;
	declare b VARCHAR(255);
	SET c = NEW.course_term_id; 
	SET b = (SELECT term_name FROM term WHERE term_id = c);
  SET new.course_term = b;
	IF((SELECT term_status FROM term WHERE term_id = c)=1) THEN
		SET NEW.course_status = 1;
	END IF;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
